package com.example.demo.mapper;

import com.example.demo.dto.StudentCourseInfo;
import com.example.demo.entity.Student;
import com.example.demo.entity.StudentCourse;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudentMapper {
    // 新增学生
    @Insert("INSERT INTO students (user_id, real_name, enrolled_date) VALUES (#{user_id}, #{real_name}, #{enrolled_date})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertStudent(Student student);

    // 删除学生
    @Delete("DELETE FROM students WHERE id = #{id}")
    int deleteStudentById(int id);

    // 修改学生
    @Update("UPDATE students SET user_id = #{user_id}, real_name = #{real_name}, enrolled_date = #{enrolled_date} WHERE id = #{id}")
    int updateStudent(Student student);

    // 查询学生
    @Select("SELECT * FROM students WHERE id = #{id}")
    Student selectStudentById(int id);

    // 查询所有学生
    @Select("SELECT * FROM students")
    List<Student> selectAllStudents();

    // 联合查询学生及其选课信息
    @Select("SELECT s.*, sc.course_id, sc.enrolled_at FROM students s " +
            "LEFT JOIN student_courses sc ON s.id = sc.student_id " +
            "WHERE s.id = #{studentId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "user_id", column = "user_id"),
            @Result(property = "real_name", column = "real_name"),
            @Result(property = "enrolled_date", column = "enrolled_date"),
//            @Result(property = "studentCourses", javaType = List.class,
//                    many = @Many(select = "com.example.demo.mapper.StudentCourseMapper.selectByStudentId"))
    })
    Student selectStudentWithCourses(int studentId);

    @Select("" +
            "SELECT " +
            "u.id AS user_id, " +
            "u.username, " +
            "s.real_name AS student_name, " +
            "c.id AS course_id, " +
            "c.title AS course_title, " +
            "c.description AS course_description, " +
            "sc.enrolled_at AS enroll_time " +
            "FROM users u " +
            "LEFT JOIN students s ON u.id = s.user_id " +
            "LEFT JOIN student_courses sc ON s.id = sc.student_id " +
            "LEFT JOIN courses c ON sc.course_id = c.id " +
            "WHERE u.id = #{studentId}"
    )
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "username", column = "username"),
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "courseId", column = "course_id"),
            @Result(property = "courseTitle", column = "course_title"),
            @Result(property = "courseDescription", column = "course_description"),
            @Result(property = "enrollTime", column = "enroll_time")
    })
    List<StudentCourseInfo> getCoursesByStudentId(@Param("studentId") Integer studentId);
}
